#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <time.h>
#include <ctype.h>
#include <sqlite3.h>
#include <stdbool.h>  // 添加布尔类型支持

#define MAX_SENTENCES  12
#define MAX_SEARCHED_STUDENTS 10  // 最大查询学生数量

// 定义账户类型
typedef enum {
    ACCOUNT_TYPE_STUDENT = 0,
    ACCOUNT_TYPE_ADMIN = 1
} AccountType;

sqlite3 *db;

// 全局变量，用于记录查询过的学生
typedef struct {
    char id[50];
    char name[50];
    float chinese;
    float math;
    float english;
    float average;
} StudentRecord;

StudentRecord searched_students[MAX_SEARCHED_STUDENTS];
int searched_count = 0;

// 错误处理函数
void handle_error(const char *msg, int rc) {
    if (rc != SQLITE_OK) {
        fprintf(stderr, "%s: %s\n", msg, sqlite3_errmsg(db));
    }
}

// 验证密码复杂度
bool validate_password(const char *password) {
    int hasUpper = 0, hasLower = 0, hasDigit = 0;
    int length = strlen(password);
    if (length < 8) return false;

    for (int i = 0; password[i] != '\0'; i++) {
        if (isupper((unsigned char)password[i])) hasUpper = 1;
        if (islower((unsigned char)password[i])) hasLower = 1;
        if (isdigit((unsigned char)password[i])) hasDigit = 1;
    }

    return hasUpper && hasLower && hasDigit;
}

// 安全输入函数
bool safe_input(char *input, int size, const char *prompt) {
    printf("%s", prompt);
    if (fgets(input, size, stdin) == NULL) {
        printf("输入错误，请重新输入。\n");
        return false;
    }
    // 去除换行符
    input[strcspn(input, "\n")] = 0;
    return true;
}

// 加密密码函数（简单示例，实际应用需更安全的加密方法）
void encrypt_password(const char *password, char *encrypted) {
    strcpy(encrypted, password); // 这里简单复制，实际应使用加密算法
}

// 创建用户函数 - 修复错误处理
int create_user(const char *username, const char *password, AccountType account_type, const char *user_info) {
    sqlite3_stmt *stmt;
    const char *sql = "INSERT INTO Users(username, password, account_type, user_info) VALUES(?, ?, ?, ?)";
    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
    if (rc != SQLITE_OK) {
        printf("SQL准备失败: %s\n", sqlite3_errmsg(db));
        return rc;
    }

    sqlite3_bind_text(stmt, 1, username, -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 2, password, -1, SQLITE_STATIC);
    sqlite3_bind_int(stmt, 3, account_type);
    sqlite3_bind_text(stmt, 4, user_info, -1, SQLITE_STATIC);

    rc = sqlite3_step(stmt);
    if (rc != SQLITE_DONE) {
        printf("SQL执行失败: %s\n", sqlite3_errmsg(db));
        if (rc == SQLITE_CONSTRAINT) {
            printf("具体错误: 违反约束（可能是用户名已存在）\n");
        }
    }
    sqlite3_finalize(stmt);
    return rc;
}

// 用户登录函数
bool user_login(const char *username, const char *encrypted_password, AccountType *account_type) {
    sqlite3_stmt *stmt;
    const char *sql = "SELECT account_type FROM Users WHERE username =? AND password =?";
    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
    if (rc != SQLITE_OK) return false;

    sqlite3_bind_text(stmt, 1, username, -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 2, encrypted_password, -1, SQLITE_STATIC);

    if (sqlite3_step(stmt) == SQLITE_ROW) {
        *account_type = (AccountType)sqlite3_column_int(stmt, 0);
        sqlite3_finalize(stmt);
        return true;
    }

    sqlite3_finalize(stmt);
    return false;
}

// 修改用户密码函数 - 增强错误处理
int update_user_password(const char *username, const char *new_password) {
    sqlite3_stmt *stmt;
    const char *sql = "UPDATE Users SET password =? WHERE username =?";
    int rc;
    
    // 开始事务
    rc = sqlite3_exec(db, "BEGIN TRANSACTION;", NULL, 0, NULL);
    if (rc != SQLITE_OK) {
        printf("开始事务失败: %s\n", sqlite3_errmsg(db));
        return rc;
    }
    
    // 准备SQL语句
    rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
    if (rc != SQLITE_OK) {
        printf("SQL准备失败: %s\n", sqlite3_errmsg(db));
        sqlite3_exec(db, "ROLLBACK;", NULL, 0, NULL);
        return rc;
    }

    char encrypted[100];
    encrypt_password(new_password, encrypted);
    
    sqlite3_bind_text(stmt, 1, encrypted, -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 2, username, -1, SQLITE_STATIC);

    // 执行SQL语句
    rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    
    if (rc != SQLITE_DONE) {
        printf("SQL执行失败: %s\n", sqlite3_errmsg(db));
        sqlite3_exec(db, "ROLLBACK;", NULL, 0, NULL);
        return rc;
    }
    
    // 提交事务
    rc = sqlite3_exec(db, "COMMIT;", NULL, 0, NULL);
    if (rc != SQLITE_OK) {
        printf("提交事务失败: %s\n", sqlite3_errmsg(db));
        return rc;
    }
    
    // 检查是否有行被更新
    int changes = sqlite3_changes(db);
    if (changes == 0) {
        printf("没有找到用户: %s\n", username);
        return SQLITE_ERROR;
    }
    
    return SQLITE_OK;
}

// 修改用户信息函数
int update_user_info(const char *username, const char *new_info) {
    sqlite3_stmt *stmt;
    const char *sql = "UPDATE Users SET user_info =? WHERE username =?";
    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
    if (rc != SQLITE_OK) return rc;

    sqlite3_bind_text(stmt, 1, new_info, -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 2, username, -1, SQLITE_STATIC);

    rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    return rc;
}

// 修改用户账户类型函数
int change_user_account_type(const char *username, AccountType account_type) {
    sqlite3_stmt *stmt;
    const char *sql = "UPDATE Users SET account_type =? WHERE username =?";
    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
    if (rc != SQLITE_OK) return rc;

    sqlite3_bind_int(stmt, 1, account_type);
    sqlite3_bind_text(stmt, 2, username, -1, SQLITE_STATIC);

    rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    return rc;
}

// 验证用户密码
bool verify_user_password(const char *username, const char *password) {
    sqlite3_stmt *stmt;
    const char *sql = "SELECT password FROM Users WHERE username =?";
    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
    if (rc != SQLITE_OK) return false;

    sqlite3_bind_text(stmt, 1, username, -1, SQLITE_STATIC);

    if (sqlite3_step(stmt) == SQLITE_ROW) {
        const char *stored_password = (const char*)sqlite3_column_text(stmt, 0);
        char encrypted[100];
        encrypt_password(password, encrypted);
        
        bool is_valid = (strcmp(stored_password, encrypted) == 0);
        sqlite3_finalize(stmt);
        return is_valid;
    }

    sqlite3_finalize(stmt);
    return false;
}

// 回调函数，用于显示查询结果
static int callback(void *NotUsed, int argc, char **argv, char **azColName) {
    for (int i = 0; i < argc; i++) {
        printf("%s = %s\t", azColName[i], argv[i] ? argv[i] : "NULL");
    }
    printf("\n");
    return 0;
}

// 回调函数：显示成绩表
static int displayScoresCallback(void *NotUsed, int argc, char **argv, char **azColName) {
    // 确保有足够的列
    if (argc >= 6) {
        // 处理可能为NULL的成绩字段
        double chinese = (argv[2] != NULL) ? atof(argv[2]) : 0.0;
        double math = (argv[3] != NULL) ? atof(argv[3]) : 0.0;
        double english = (argv[4] != NULL) ? atof(argv[4]) : 0.0;
        double average = (argv[5] != NULL) ? atof(argv[5]) : 0.0;
        
        // 格式化输出，保持列对齐
        printf("%-8s %-10s %6.1f %6.1f %6.1f %6.1f\n", 
               argv[0] ? argv[0] : "未知",  // 学号
               argv[1] ? argv[1] : "未知",  // 姓名
               chinese, math, english, average);
    }
    return 0;
}

// 回调函数：显示排序后的成绩表
static int displaySortedScoresCallback(void *NotUsed, int argc, char **argv, char **azColName) {
    // 确保有足够的列
    if (argc >= 6) {
        // 处理可能为NULL的成绩字段
        double chinese = (argv[2] != NULL) ? atof(argv[2]) : 0.0;
        double math = (argv[3] != NULL) ? atof(argv[3]) : 0.0;
        double english = (argv[4] != NULL) ? atof(argv[4]) : 0.0;
        double average = (argv[5] != NULL) ? atof(argv[5]) : 0.0;
        
        // 格式化输出，保持列对齐
        printf("%-8s %-10s %6.1f %6.1f %6.1f %6.1f\n", 
               argv[0] ? argv[0] : "未知",  // 学号
               argv[1] ? argv[1] : "未知",  // 姓名
               chinese, math, english, average);
    }
    return 0;
}

// 初始化数据库
void initDB(sqlite3 **db) {
    char *errMsg = 0;
    int rc = sqlite3_open("students.db", db);  // 修复参数类型错误

    if (rc) {
        fprintf(stderr, "无法打开数据库: %s\n", sqlite3_errmsg(*db));
        exit(0);
    } else {
        fprintf(stdout, "成功打开数据库\n");
    }

    // 创建学生表（如果不存在）
    const char *createTableSQL = "CREATE TABLE IF NOT EXISTS students ("
                                "id TEXT PRIMARY KEY,"
                                "name TEXT NOT NULL,"
                                "class TEXT NOT NULL);";

    rc = sqlite3_exec(*db, createTableSQL, callback, 0, &errMsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL错误: %s\n", errMsg);
        sqlite3_free(errMsg);
    } else {
        fprintf(stdout, "表创建成功或已存在\n");
    }

    // 创建用户表（如果不存在）
    const char *sql_create_users = "CREATE TABLE IF NOT EXISTS Users("
                                   "id INTEGER PRIMARY KEY AUTOINCREMENT,"
                                   "username TEXT UNIQUE NOT NULL,"
                                   "password TEXT NOT NULL,"
                                   "account_type INTEGER DEFAULT 0,"
                                   "user_info TEXT)";
    sqlite3_exec(*db, sql_create_users, NULL, NULL, NULL);

    // 创建默认管理员账户（如果不存在）
    sqlite3_stmt *stmt;
    const char *sql_check_admin = "SELECT COUNT(*) FROM Users WHERE account_type = 1";
    rc = sqlite3_prepare_v2(*db, sql_check_admin, -1, &stmt, 0);

    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL错误: %s\n", sqlite3_errmsg(*db));
    } else if (sqlite3_step(stmt) == SQLITE_ROW) {
        int count = sqlite3_column_int(stmt, 0);
        sqlite3_finalize(stmt);

        if (count == 0) {
            char encrypted[100];
            encrypt_password("admin123", encrypted);
            int rc = create_user("admin", encrypted, ACCOUNT_TYPE_ADMIN, "系统默认管理员");
            if (rc == SQLITE_OK || rc == SQLITE_DONE) {
                printf("已创建默认管理员账户: 用户名=admin, 密码=admin123\n");
            } else {
                printf("创建默认管理员账户失败: %s\n", sqlite3_errmsg(*db));
            }
        } else {
            printf("默认管理员账户已存在\n");
        }
    } else {
        sqlite3_finalize(stmt);
    }

    // 创建成绩表（如果不存在）
    const char *sql_create_scores = "CREATE TABLE IF NOT EXISTS scores ("
                                   "id INTEGER PRIMARY KEY AUTOINCREMENT,"
                                   "student_id TEXT NOT NULL,"
                                   "chinese REAL,"
                                   "math REAL,"
                                   "english REAL,"
                                   "average REAL,"
                                   "FOREIGN KEY (student_id) REFERENCES students(id))";
    sqlite3_exec(*db, sql_create_scores, NULL, NULL, NULL);
}

// 录入学生信息
void addStudent(sqlite3 *db) {
    char id[50], name[50], class[50];
    char sql[200];
    char *errMsg = 0;

    printf("请输入学生学号：");
    scanf("%s", id);
    printf("请输入学生姓名：");
    scanf("%s", name);
    printf("请输入学生班级：");
    scanf("%s", class);

    // 构建SQL插入语句
    snprintf(sql, sizeof(sql),"INSERT INTO students (id, name, class) "
                 "VALUES ('%s', '%s', '%s');", id, name, class);

    int rc = sqlite3_exec(db, sql, callback, 0, &errMsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL错误: %s\n", errMsg);
        sqlite3_free(errMsg);
    } else {
        fprintf(stdout, "学生信息录入成功\n");
    }
}

// 更新学生信息
void updateStudent(sqlite3 *db) {
    char id[50], name[50], class[50];
    char sql[200];
    char *errMsg = 0;

    printf("请输入要更新的学生学号：");
    scanf("%s", id);

    // 先检查学生是否存在
    sprintf(sql, "SELECT * FROM students WHERE id='%s';", id);
    int rc = sqlite3_exec(db, sql, callback, 0, &errMsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL错误: %s\n", errMsg);
        sqlite3_free(errMsg);
        return;
    }

    printf("请输入新的学生姓名：");
    scanf("%s", name);
    printf("请输入新的学生班级：");
    scanf("%s", class);

    // 构建SQL更新语句
    snprintf(sql,sizeof(sql), "UPDATE students SET name='%s', class='%s' WHERE id='%s';", name, class, id);
    rc = sqlite3_exec(db, sql, callback, 0, &errMsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL错误: %s\n", errMsg);
        sqlite3_free(errMsg);
    } else {
        fprintf(stdout, "学生信息更新成功\n");
    }
}

// 删除学生信息
void deleteStudent(sqlite3 *db) {
    char id[50];
    char sql[100];
    char *errMsg = 0;

    printf("请输入要删除的学生学号：");
    scanf("%s", id);

    // 先删除相关成绩
    sprintf(sql, "DELETE FROM scores WHERE student_id='%s';", id);
    sqlite3_exec(db, sql, NULL, NULL, NULL);

    // 再删除学生信息
    sprintf(sql, "DELETE FROM students WHERE id='%s';", id);
    int rc = sqlite3_exec(db, sql, callback, 0, &errMsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL错误: %s\n", errMsg);
        sqlite3_free(errMsg);
    } else {
        fprintf(stdout, "学生信息删除成功\n");
    }
}

// 显示所有学生信息
void displayAllStudents(sqlite3 *db) {
    const char *sql = "SELECT * FROM students;";
    char *errMsg = 0;

    printf("所有学生信息如下：\n");
    int rc = sqlite3_exec(db, sql, callback, 0, &errMsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL错误: %s\n", errMsg);
        sqlite3_free(errMsg);
    }
}

// 数据库版本：录入学生成绩
int Type(sqlite3 *db)
{
    char id[50];
    float chinese, math, english;
    char sql[200];
    char *errMsg = 0;
    int count = 0;

    printf("请输入要录入成绩的学生人数：");
    scanf("%d", &count);
    if (count <= 0)
    {
        printf("人数必须大于0！\n");
        return 0;
    }

    printf("请输入%d名学生的三门课成绩\n", count);
    printf("学号  语文  数学  外语\n");

    for (int i = 0; i < count; i++)
    {
        printf("学生%d: ", i + 1);
        scanf("%s %f %f %f", id, &chinese, &math, &english);

        // 检查学生是否存在
        sprintf(sql, "SELECT * FROM students WHERE id='%s';", id);
        sqlite3_stmt *stmt;
        int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
        if (rc != SQLITE_OK) {
            fprintf(stderr, "SQL错误: %s\n", sqlite3_errmsg(db));
            continue;
        }

        if (sqlite3_step(stmt) != SQLITE_ROW) {
            printf("学生 %s 不存在，请先添加学生信息！\n", id);
            sqlite3_finalize(stmt);
            continue;
        }
        sqlite3_finalize(stmt);

        // 计算平均分
        float average = (chinese + math + english) / 3;

        // 检查成绩是否已存在，存在则更新，不存在则插入
        sprintf(sql, "SELECT * FROM scores WHERE student_id='%s';", id);
        rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);

        if (rc != SQLITE_OK) {
            fprintf(stderr, "SQL错误: %s\n", sqlite3_errmsg(db));
            continue;
        }

        if (sqlite3_step(stmt) == SQLITE_ROW) {
            // 更新成绩
            sqlite3_finalize(stmt);
            sprintf(sql, "UPDATE scores SET chinese=%f, math=%f, english=%f, average=%f WHERE student_id='%s';", 
                    chinese, math, english, average, id);
        } else {
            // 插入新成绩
            sqlite3_finalize(stmt);
            sprintf(sql, "INSERT INTO scores (student_id, chinese, math, english, average) VALUES ('%s', %f, %f, %f, %f);", 
                    id, chinese, math, english, average);
        }

        rc = sqlite3_exec(db, sql, NULL, NULL, &errMsg);
        if (rc != SQLITE_OK) {
            fprintf(stderr, "SQL错误: %s\n", errMsg);
            sqlite3_free(errMsg);
        } else {
            printf("学生 %s 的成绩录入成功\n", id);
        }
    }

    return count;
}

// 数据库版本：计算学生平均分
void Average(sqlite3 *db)
{
    char sql[200];
    char *errMsg = 0;

    // 更新所有学生的平均分
    sprintf(sql, "UPDATE scores SET average = (chinese + math + english) / 3;");
    int rc = sqlite3_exec(db, sql, NULL, NULL, &errMsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL错误: %s\n", errMsg);
        sqlite3_free(errMsg);
    } else {
        printf("所有学生的平均分已更新\n");
    }
}

// 数据库版本：列出成绩表
void List(sqlite3 *db)
{
    const char *sql = "SELECT s.id, s.name, sc.chinese, sc.math, sc.english, sc.average "
                      "FROM students s "
                      "LEFT JOIN scores sc ON s.id = sc.student_id "
                      "ORDER BY s.id;";
    char *errMsg = 0;

    printf("学生成绩如下：\n");
    printf("%-8s %-10s %6s %6s %6s %6s\n", "学号", "姓名", "语文", "数学", "外语", "平均分");
    printf("----------------------------------------------------------------\n");
    
    int rc = sqlite3_exec(db, sql, displayScoresCallback, 0, &errMsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL错误: %s\n", errMsg);
        sqlite3_free(errMsg);
    }
}

// 数据库版本：按平均成绩由高到低排序
void Sort(sqlite3 *db)
{
    const char *sql = "SELECT s.id, s.name, sc.chinese, sc.math, sc.english, sc.average "
                      "FROM students s "
                      "LEFT JOIN scores sc ON s.id = sc.student_id "
                      "ORDER BY sc.average DESC;";
    char *errMsg = 0;

    printf("学生成绩按平均分由高到低排序如下：\n");
    printf("%-8s %-10s %6s %6s %6s %6s\n", "学号", "姓名", "语文", "数学", "外语", "平均分");
    printf("----------------------------------------------------------------\n");
    
    int rc = sqlite3_exec(db, sql, displaySortedScoresCallback, 0, &errMsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL错误: %s\n", errMsg);
        sqlite3_free(errMsg);
    }
}

// 数据库版本：按学号查询学生成绩
void Search(sqlite3 *db) {
    char id[50];
    char sql[200];
    char *errMsg = 0;

    printf("请输入要查询的学生学号：");
    scanf("%s", id);

    sprintf(sql, "SELECT s.id, s.name, sc.chinese, sc.math, sc.english, sc.average "
             "FROM students s "
             "LEFT JOIN scores sc ON s.id = sc.student_id "
             "WHERE s.id = '%s';", id);

    printf("学生 %s 的成绩如下：\n", id);
    printf("%-8s %-10s %6s %6s %6s %6s\n", "学号", "姓名", "语文", "数学", "外语", "平均分");
    printf("----------------------------------------------------------------\n");
    
    sqlite3_stmt *stmt;
    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL错误: %s\n", sqlite3_errmsg(db));
        return;
    }

    if (sqlite3_step(stmt) == SQLITE_ROW) {
        // 记录查询结果
        if (searched_count < MAX_SEARCHED_STUDENTS) {
            strcpy(searched_students[searched_count].id, (const char*)sqlite3_column_text(stmt, 0));
            strcpy(searched_students[searched_count].name, (const char*)sqlite3_column_text(stmt, 1));
            searched_students[searched_count].chinese = sqlite3_column_double(stmt, 2);
            searched_students[searched_count].math = sqlite3_column_double(stmt, 3);
            searched_students[searched_count].english = sqlite3_column_double(stmt, 4);
            searched_students[searched_count].average = sqlite3_column_double(stmt, 5);
            searched_count++;
        }

        // 显示结果
        int argc = sqlite3_column_count(stmt);
        char **argv = (char **)malloc(argc * sizeof(char *));
        char **azColName = (char **)malloc(argc * sizeof(char *));
        
        for (int i = 0; i < argc; i++) {
            azColName[i] = (char *)sqlite3_column_name(stmt, i);
            const char *columnText = (const char *)sqlite3_column_text(stmt, i);
            argv[i] = columnText ? strdup(columnText) : strdup("NULL");
        }
        
        displayScoresCallback(NULL, argc, argv, azColName);
    } else {
        printf("未找到学号为 %s 的学生成绩\n", id);
    }

    sqlite3_finalize(stmt);
}

// 打印查询过的学生成绩报告
void printSearchedStudentsReport() {
    if (searched_count == 0) {
        printf("没有查询过的学生记录\n");
        return;
    }

    printf("\n===== 查询过的学生成绩报告 =====\n");
    printf("%-8s %-10s %6s %6s %6s %6s\n", "学号", "姓名", "语文", "数学", "外语", "平均分");
    printf("----------------------------------------------------------------\n");
    
    for (int i = 0; i < searched_count; i++) {
        printf("%-8s %-10s %6.1f %6.1f %6.1f %6.1f\n", 
               searched_students[i].id,
               searched_students[i].name,
               searched_students[i].chinese,
               searched_students[i].math,
               searched_students[i].english,
               searched_students[i].average);
    }
}

// 导出查询过的学生成绩报告到文本文件
void exportSearchedStudentsReport() {
    if (searched_count == 0) {
        printf("没有查询过的学生记录可以导出\n");
        return;
    }

    FILE *fp = fopen("student_report.txt", "w");
    if (fp == NULL) {
        printf("无法创建报告文件\n");
        return;
    }

    fprintf(fp, "===== 学生成绩报告 =====\n");
    fprintf(fp, "生成时间: %s", ctime(&(time_t){time(NULL)}));
    fprintf(fp, "%-8s %-10s %6s %6s %6s %6s\n", "学号", "姓名", "语文", "数学", "外语", "平均分");
    fprintf(fp, "----------------------------------------------------------------\n");
    
    for (int i = 0; i < searched_count; i++) {
        fprintf(fp, "%-8s %-10s %6.1f %6.1f %6.1f %6.1f\n", 
               searched_students[i].id,
               searched_students[i].name,
               searched_students[i].chinese,
               searched_students[i].math,
               searched_students[i].english,
               searched_students[i].average);
    }

    fclose(fp);
    printf("已成功导出学生成绩报告到 student_report.txt\n");
}

// 学生查询自己成绩 - 修改为直接输入学号查询
void SearchMyScore(sqlite3 *db) {
    char student_id[50];
    char sql[200];
    char *errMsg = 0;

    // 让学生直接输入学号
    printf("请输入你的学号查询成绩：");
    scanf("%s", student_id);

    // 查询该学生的成绩
    sprintf(sql, "SELECT s.id, s.name, sc.chinese, sc.math, sc.english, sc.average "
                 "FROM students s "
                 "LEFT JOIN scores sc ON s.id = sc.student_id "
                 "WHERE s.id = '%s';", student_id);

    //随机生成句子
    const char *sentences[MAX_SENTENCES] = {
        "慢也好，步子小也好，往前走就好。",
        "答案在路上，自由在风里。",
        "同一片风景，高度不同，感受亦不同。",
        "迎万难，走万难。",
        "等风来，不如追风去。",
        "心向往之行必能至。",
        "一念既出万山无阻",
        "关关难过关关过，前途漫漫亦灿灿。",
        "不想满心遗憾，那就拼尽全力。",
        "屈指可数的日子里，我们都要坚持下去。",
        "你不是暗淡渺小的星光，而是囊括了明月的整个银河。",
        "乾坤未定，你我皆是黑马。"
    };

    // 初始化随机数种子
    srand(time(NULL));

    // 随机选择一条句子
    int random_index = rand() % MAX_SENTENCES;
    printf("你的成绩如下：\n");
    printf("%-8s %-10s %6s %6s %6s %6s\n", "学号", "姓名", "语文", "数学", "外语", "平均分");
    printf("----------------------------------------------------------------\n");

    int rc = sqlite3_exec(db, sql, displayScoresCallback, 0, &errMsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL错误: %s\n", errMsg);
        sqlite3_free(errMsg);
    }
    printf("%s\n", sentences[random_index]);
    
    // 记录查询结果（用于导出报告）
    sqlite3_stmt *stmt;
    rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
    if (rc == SQLITE_OK && sqlite3_step(stmt) == SQLITE_ROW) {
        if (searched_count < MAX_SEARCHED_STUDENTS) {
            strcpy(searched_students[searched_count].id, (const char*)sqlite3_column_text(stmt, 0));
            strcpy(searched_students[searched_count].name, (const char*)sqlite3_column_text(stmt, 1));
            searched_students[searched_count].chinese = sqlite3_column_double(stmt, 2);
            searched_students[searched_count].math = sqlite3_column_double(stmt, 3);
            searched_students[searched_count].english = sqlite3_column_double(stmt, 4);
            searched_students[searched_count].average = sqlite3_column_double(stmt, 5);
            searched_count++;
        }
    }
    sqlite3_finalize(stmt);
}

// 学生修改个人信息
void studentModifyInfo(sqlite3 *db, const char *username) {
    int choice;
    char new_info[200];
    char old_password[100], new_password[100], confirm_password[100];
    int rc;
    
    do {
        printf("\n===== 学生信息修改 =====\n");
        printf("当前用户: %s\n", username);
        printf("请选择要修改的内容：\n");
        printf("1. 修改个人信息\n");
        printf("2. 修改密码\n");
        printf("0. 返回上一级菜单\n");
        printf("请输入选择：");
        
        if (scanf("%d", &choice) != 1) {
            printf("无效输入！\n");
            while (getchar() != '\n'); // 清除输入缓冲区
            continue;
        }
        getchar(); // 消耗换行符
        
        switch (choice) {
            case 1: // 修改个人信息
                if (!safe_input(new_info, sizeof(new_info), "请输入新的个人信息："))
                    continue;
                
                rc = update_user_info(username, new_info);
                if (rc == SQLITE_OK) {
                    printf("个人信息更新成功！\n");
                } else {
                    printf("更新失败：发生错误！\n");
                }
                break;
                
            case 2: // 修改密码
                // 验证旧密码
                if (!safe_input(old_password, sizeof(old_password), "请输入当前密码："))
                    continue;

                if (!verify_user_password(username, old_password)) {
                    printf("当前密码错误！\n");
                    break;
                }
                
                // 输入新密码
                do {
                    if (!safe_input(new_password, sizeof(new_password), "请输入新密码(需包含大小写字母和数字，至少8位)："))
                        continue;
                    
                    if (!validate_password(new_password)) {
                        printf("密码不符合要求！请重新输入。\n");
                        continue;
                    }
                    
                    if (!safe_input(confirm_password, sizeof(confirm_password), "请确认新密码："))
                        continue;
                    
                    if (strcmp(new_password, confirm_password) != 0) {
                        printf("两次输入的密码不一致！请重新输入。\n");
                        continue;
                    }
                    
                    break;
                } while (1);
                
                // 更新密码
                rc = update_user_password(username, new_password);
                if (rc == SQLITE_OK) {
                    printf("密码更新成功！请使用新密码登录。\n");
                    // 密码修改成功后，需要重新登录
                    return;
                } else {
                    printf("密码更新失败：发生错误！\n");
                }
                break;
                
            case 0: // 返回上一级菜单
                printf("返回上一级菜单\n");
                break;
                
            default:
                printf("无效选择，请重新输入！\n");
        }
    } while (choice != 0);
}

// 帮助菜单
void Help()
{
     printf("****************************************************\n");
     printf("  *             学生管理系统——帮助菜单            *   \n");
     printf("  *              H=显示帮助菜单                   *   \n");
     printf("  *              T=成绩录入                      *   \n");
     printf("  *              A=计算学生平均分                *   \n");
     printf("  *              L=列出成绩表                   *   \n");
     printf("  *              P=按平均成绩由高到低排序         *   \n");
     printf("  *              S=按学号查询学生成绩            *   \n");
     printf("  *              C=清屏                         *   \n");
     printf("  *              Q=退出系统                    *    \n");
     printf("****************************************************\n");
}

int main()
{
    int rc = sqlite3_open("students.db", &db);  // 修复参数类型错误
    if (rc != SQLITE_OK)
    {
        handle_error("无法打开数据库", rc);
        return 1;
    }

    initDB(&db);

    int choice;
    char username[50], password[50], encrypted[100];
    char user_info[200];
    AccountType account_type;
    int running = 1;
    int is_logged_in = 0;
    char current_username[50] = {0};

    printf("===== 用户管理系统 =====\n");

    while (running)
    {
        if (!is_logged_in)
        {
            printf("\n请选择操作：\n");
            printf("1. 用户注册\n");
            printf("2. 用户登录\n");
            printf("3. 退出程序\n");
            printf("请输入选择：");

            if (scanf("%d", &choice) != 1)
            {
                printf("无效输入！\n");
                while (getchar() != '\n'); // 清除输入缓冲区
                continue;
            }
            getchar(); // 消耗换行符

            switch (choice)
            {
            case 1: // 用户注册
                if (!safe_input(username, sizeof(username), "请输入用户名："))
                    continue;

                // 循环输入直到密码满足复杂度要求
                do
                {
                    if (!safe_input(password, sizeof(password), "请输入密码(需包含大小写字母和数字，至少8位)："))
                        continue;
                } while (!validate_password(password));


                encrypt_password(password, encrypted);

                // 新用户默认为学生账户
                rc = create_user(username, encrypted, ACCOUNT_TYPE_STUDENT, "");
                if (rc == SQLITE_OK || rc == SQLITE_DONE)
                {
                    printf("学生账户注册成功！\n");
                }
                else if (rc == SQLITE_CONSTRAINT)
                {
                    printf("注册失败：用户名已存在！\n");
                }
                else
                {
                    printf("注册失败：未知错误！\n");
                }
                break;

            case 2: // 用户登录
                if (!safe_input(username, sizeof(username), "请输入用户名："))
                    continue;
                if (!safe_input(password, sizeof(password), "请输入密码："))
                    continue;

                encrypt_password(password, encrypted);

                if (user_login(username, encrypted, &account_type))
                {
                    printf("登录成功！\n");
                    printf("账户类型：%s\n", account_type == ACCOUNT_TYPE_ADMIN ? "管理员" : "学生");

                    is_logged_in = 1;
                    strcpy(current_username, username);
                }
                else
                {
                    printf("登录失败：用户名或密码错误！\n");
                }
                break;


            case 3: // 退出程序
                running = 0;
                printf("感谢使用，再见\n");
                break;

            default:
                printf("无效选择，请重新输入！\n");
            }
        }
        else
        {
            if (account_type == ACCOUNT_TYPE_ADMIN)
            {
                // 管理员菜单
                printf("\n===== 管理员菜单 =====\n");
                printf("当前用户: %s\n", current_username);
                printf("请选择操作：\n");
                printf("4. 修改用户账户类型\n");
                printf("5. 修改个人信息\n");
                printf("6. 学生信息管理系统\n");
                printf("7. 学生成绩管理系统\n");
                printf("0. 退出登录\n");
                printf("请输入选择：");

                if (scanf("%d", &choice) != 1)
                {
                    printf("无效输入！\n");
                    while (getchar() != '\n'); // 清除输入缓冲区
                    continue;
                }
                getchar(); // 消耗换行符

                switch (choice)
                {
                case 4: // 修改用户账户类型
                    if (!safe_input(username, sizeof(username), "请输入要修改的用户名："))
                        continue;


                    if (strcmp(username, current_username) == 0)
                    {
                        printf("不能修改当前登录的用户类型！\n");
                        continue;
                    }

                    printf("请选择新的账户类型 (0-学生, 1-管理员)： ");
                    if (scanf("%d", &choice) != 1)
                    {
                        printf("无效输入！\n");
                        while (getchar() != '\n');
                        continue;
                    }
                    getchar(); // 消耗换行符

                    AccountType new_account_type = (choice == 1) ? ACCOUNT_TYPE_ADMIN : ACCOUNT_TYPE_STUDENT;

                    rc = change_user_account_type(username, new_account_type);
                    if (rc == SQLITE_OK)
                    {
                        printf("用户账户类型修改成功！\n");
                    }
                    else
                    {
                        printf("修改失败：用户不存在或发生错误！\n");
                    }
                    break;

                case 5: // 修改个人信息
                    if (!safe_input(user_info, sizeof(user_info), "请输入新的个人信息："))
                        continue;

                    rc = update_user_info(current_username, user_info);

                    if (rc == SQLITE_OK)
                    {
                        printf("个人信息更新成功！\n");
                    }
                    else
                    {
                        printf("更新失败：发生错误！\n");
                    }
                    break;

                case 6: // 学生信息管理系统
                    do
                    {
                        printf("\n学生信息管理系统\n");
                        printf("------------------------\n");
                        printf("1. 录入学生信息\n");
                        printf("2. 更新学生信息\n");
                        printf("3. 删除学生信息\n");
                        printf("4. 显示所有学生信息\n");
                        printf("0. 返回上一级菜单\n");
                        printf("------------------------\n");
                        printf("请输入你的选择：");
                        scanf("%d", &choice);

                        switch (choice)
                        {
                        case 1:
                            addStudent(db);
                            break;
                        case 2:
                            updateStudent(db);
                            break;
                        case 3:
                            deleteStudent(db);
                            break;
                        case 4:
                            displayAllStudents(db);
                            break;

                        case 0:
                            printf("返回上一级菜单\n");
                            break;
                        default:
                            printf("无效的选择，请重新输入！\n");
                        }
                    } while (choice != 0);
                    break;

                case 7: // 学生成绩管理系统
                {
                    char a;
                    Help();

                    while (1)
                    {
                        printf("请输入命令：");
                        scanf(" %c", &a);
                        if (a == 'C')
                        {
                            system("clear");
                        }
                        if (a == 'H')
                        {
                            Help();
                        }
                        if (a == 'Q')
                        {
                            break;
                        }
                        if (a != 'T' && a != 'H' && a != 'C' && a != 'Q')
                        {
                            // 检查是否有学生成绩
                            const char *sql = "SELECT COUNT(*) FROM scores;";
                            sqlite3_stmt *stmt;
                            int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
                            if (rc == SQLITE_OK && sqlite3_step(stmt) == SQLITE_ROW) {
                                int count = sqlite3_column_int(stmt, 0);
                                sqlite3_finalize(stmt);
                                
                                if (count == 0) {
                                    printf("成绩表为空！请先使用命令T录入学生成绩。\n");
                                    continue;
                                }
                            }
                        }

                        
                        switch (a) {
                            case 'T':
                                Type(db);
                                break;
                            case 'L':
                                List(db);
                                break;
                            case 'A':
                                Average(db);
                                break;
                            case 'P':
                                Sort(db);
                                break;
                            case 'S':
                                Search(db);
                                break;
                            default:
                                if (a != 'H' && a != 'C' && a != 'Q')
                                    printf("无效命令！请重新输入。\n");
                                break;
                        }
                        getchar();
                    }
                }
                    break;

                case 0: // 退出登录
                    if (account_type == ACCOUNT_TYPE_ADMIN) {
                        // 打印并导出查询过的学生成绩报告
                        printSearchedStudentsReport();
                        exportSearchedStudentsReport();
                    }
                    is_logged_in = 0;
                    printf("已退出登录！\n");
                    break;

                default:
                    printf("无效选择，请重新输入！\n");
                }
            }
            else
            {
                // 学生菜单
                printf("\n===== 学生菜单 =====\n");
                printf("当前用户: %s\n", current_username);
                printf("请选择操作：\n");
                printf("1. 查询我的成绩\n");
                printf("2. 修改个人信息\n");
                printf("0. 退出登录\n");
                printf("请输入选择：");

                if (scanf("%d", &choice) != 1)
                {
                    printf("无效输入！\n");
                    while (getchar() != '\n'); // 清除输入缓冲区
                    continue;
                }
                getchar(); // 消耗换行符

                switch (choice)
                {
                case 1: // 查询我的成绩
                    SearchMyScore(db);
                    break;
                    
                case 2: // 修改个人信息
                    studentModifyInfo(db, current_username);
                    break;

                case 0: // 退出登录
                    is_logged_in = 0;
                    printf("已退出登录！\n");
                    break;

                default:
                    printf("无效选择，请重新输入！\n");
                }
            }
        }
    }

    sqlite3_close(db);
    return 0;
}